using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SEO.Model;
using SEO.DBSqlHelp;

namespace SEO.DAL
{

    /// <summary>
    /// 版权所有: 版权所有(C) 2011，上海云里网络科技有限公司
    /// 内容摘要: EDMMailContentDAL
    /// 完成日期：2011年7月29日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class EDMMailContentDAL
    {

        /// <summary>
        /// 获取EDMMailContent表中数据
        /// <returns>返回List</returns>
        /// </summary>
        public List<EDMMailContent> GetAllEDMMailContentDAL()
        {
            List<EDMMailContent> list = new List<EDMMailContent>();
            string strSql = "select * from EDMMailContent";
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    EDMMailContent et = new EDMMailContent();
                    et.ContentId = int.Parse(dr["content_id"].ToString());
                    et.MailTitle = dr["mail_title"].ToString();
                    et.MailContent = dr["mail_content"].ToString();
                    et.MailAttachment = dr["mail_attachment"].ToString();
                    et.UserId = int.Parse(dr["user_id"].ToString());
                    et.MailStatus = int.Parse(dr["mail_status"].ToString());
                    et.PostStatus = dr["post_status"].ToString();

                    list.Add(et);
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailContent -> " + ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 根据指定SQL语句获取EDMMailContent表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回List</returns>
        public List<EDMMailContent> GetEDMMailContentByWhereDAL(string strSql)
        {
            List<EDMMailContent> list = new List<EDMMailContent>();
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    EDMMailContent et = new EDMMailContent();
                    et.ContentId = int.Parse(dr["content_id"].ToString());
                    et.MailTitle = dr["mail_title"].ToString();
                    et.MailContent = dr["mail_content"].ToString();
                    et.MailAttachment = dr["mail_attachment"].ToString();
                    et.UserId = int.Parse(dr["user_id"].ToString());
                    et.MailStatus = int.Parse(dr["mail_status"].ToString());
                    et.PostStatus = dr["post_status"].ToString();

                    list.Add(et);
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailContent -> " + ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 往EDMMailContent表中插入数据
        /// <param name="et">实例化EDMMailContent类</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int InsertEDMMailContentDAL(SEO.Model.EDMMailContent et)
        {
            int iNum = 0;
            string strSql = "insert into EDMMailContent(mail_title,mail_content,mail_attachment,user_id,mail_status) " +
             " values('" + et.MailTitle + "','" + et.MailContent + "','" + et.MailAttachment + "'," + et.UserId + "," + et.MailStatus + ")";
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailContent -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 根据指定条件删除EDMMailContent表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int DeleteEDMMailContentDAL(string strSql)
        {
            int iNum = 0;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailContent -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 更新EDMMailContent表中数据
        /// </summary>
        /// <param name="et">实例化EDMMailContent类</param>
        /// <returns>返回影响行数</returns>
        public int UpdateEDMMailContentNewDAL(SEO.Model.EDMMailContent et)
        {
            int iNum = 0;
            string strSql = "update EDMMailContent set mail_status=" + et.MailStatus + ",mail_title='" + et.MailTitle + "',mail_content='" + et.MailContent + "' where content_id=" + et.ContentId;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailContent -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 更新EDMMailContent表中数据
        /// </summary>
        /// <param name="et">实例化EDMMailContent类</param>
        /// <returns>返回影响行数</returns>
        public int UpdateEDMMailContentDAL(SEO.Model.EDMMailContent et)
        {
            int iNum = 0;
            string strSql = "update EDMMailContent set post_status=" + et.PostStatus + " where content_id=" + et.ContentId;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailContent -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 绑定表EDMMailContent到DataGridView
        /// <param name="dgv">DataGridView控件名称</param>
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        public void BindEDMMailContentDAL(System.Windows.Forms.DataGridView dgv, string strSql)
        {
            try
            {
                int iNum = 1;
                //清除最后自动生成的行
                dgv.AutoGenerateColumns = false;
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds, "EDMMailContent");
                ds.Tables["EDMMailContent"].Columns.Add("MailStatus", typeof(string));
                ds.Tables["EDMMailContent"].Columns.Add("ViewID", typeof(string));
                for (int i = 0; i < ds.Tables["EDMMailContent"].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables["EDMMailContent"].Rows[i];
                    dr["ViewID"] = iNum;
                    string strStatus = dr["mail_status"].ToString();
                    if (strStatus == "0")
                    {
                        dr["MailStatus"] = "发布";
                    }
                    else
                    {
                        dr["MailStatus"] = "不发布";
                    }
                    iNum++;
                }
                dgv.DataSource = ds.Tables["EDMMailContent"].DefaultView;
                da.Dispose();
                cn.Close();
                //清除当前选择行行为
                dgv.CurrentCell = null;

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailContent -> " + ex.Message);
            }
        }

        /// <summary>
        /// 获取表中最大ID，并自增1
        /// </summary>
        /// <returns>返回表中最大ID，并自增1</returns>
        public int GetAutoAddNum()
        {
            int iNum = 0;
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                string strSql = "select top 1 content_id from EDMMailContent order by content_id";
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["content_id"].ToString()) + 1;
                }
                else
                {
                    iNum = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定邮箱表 -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 检测是否已存在数据库中，屏蔽已抓取的数据
        /// </summary>
        /// <param name="strEmail">电子邮件</param>
        /// <returns>返回 True or False</returns>
        public bool CheckMailContentIsExist(string strEmail)
        {
            bool bValue = false;
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                string strSql = "select content_id from EDMMailContent where mail_title='" + strEmail + "'";
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    bValue = true;
                }
                else
                {
                    bValue = false;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("检测是否已存在数据库中 -> " + ex.Message);
            }

            return bValue;
        }

        /// <summary>
        /// 更新所有邮件内容为未发布状态
        /// </summary>
        /// <returns></returns>
        public int UpdateAllEDMMailContentPostStatusDAL()
        {
            int iNum = 0;
            string strSql = "update EDMMailContent set post_status=0 ";
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定更新所有邮件内容为未发布状态 -> " + ex.Message);
            }
            return iNum;
        }


        /// <summary>
        /// 导出邮件内容数据到Txt
        /// </summary>
        /// <returns>返回结果</returns>
        public string ExportEDMContentForTxt(string strUserID)
        {
            string strReturnValue = "";
            try
            {
                int k = 1;
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                string strSql = "SELECT * FROM EDMMailContent WHERE  (user_id = " + strUserID + ") ORDER BY content_id";
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    string strMailTitle = dr["mail_title"].ToString();
                    string strMailContent = dr["mail_content"].ToString();
                    string strValue = k + "	" + strMailTitle + "\r\n" + strMailContent + "\r\n\r\n\r\n";
                    strReturnValue += strValue;
                    k++;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("导出邮件内容数据到Txt -> " + ex.Message);
            }

            return strReturnValue;

        }

        /// <summary>
        /// 更新邮件内容状态
        /// </summary>
        /// <param name="strStatus">状态：0为未发布，1为发布</param>
        /// <param name="strContentID">邮件内容ID</param>
        /// <returns></returns>
        public int UpdateEDMMailContentPostStatusDAL(string strStatus,string strContentID)
        {
            int iNum = 0;
            string strSql = "update EDMMailContent set mail_status=" + strStatus + " where content_id=" + strContentID;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定更新邮件内容状态 -> " + ex.Message);
            }
            return iNum;
        }
    }
}
